from flask import Flask, request, jsonify
import sqlite3
import re
from flask_cors import CORS 
import time


app = Flask(__name__)
CORS(app, resources={
    r"/*": {
        "origins": "*",
        "methods": ["GET", "POST", "PUT", "DELETE", "OPTIONS"],
        "allow_headers": ["Content-Type", "Authorization"]
    }
})


# 数据库文件路径
DATABASE = '/Users/ljh/it/projects/stardict.db'

# 连接数据库
def get_db_connection():
    for _ in range(5):  # 最多重试5次
        try:
            conn = sqlite3.connect(DATABASE)
            conn.row_factory = sqlite3.Row
            conn.execute("PRAGMA busy_timeout = 3000")  # 等待最多3秒
            return conn
        except sqlite3.OperationalError as e:
            if 'locked' in str(e):
                time.sleep(0.5)  # 等待半秒再重试
            else:
                raise
    raise Exception("Database is locked after multiple retries")

# 获取单词定义

# 获取单词定义和翻译
def get_word_definition(word):
    conn = get_db_connection()
    cursor = conn.cursor()
    # 使用正则表达式去除非字母和数字的部分，并将字母转为小写
    sw = re.sub(r'[^a-zA-Z0-9]', '', word).lower()
    cursor.execute('SELECT translation, definition FROM stardict WHERE word = ?', (sw,))
    result = cursor.fetchone()
    conn.close()
    if result:
        return {
            'translation': result['translation'],
            'definition': result['definition']
        }
    else:
        return None

@app.route('/definition', methods=['GET'])
def definition():
    word = request.args.get('word')
    if not word:
        return jsonify({'error': 'Word parameter is required'}), 400
    result = get_word_definition(word)
    if result:
        return jsonify({
            'word': word,
            'translation': result['translation'],
            'definition': result['definition']
        })
    else:
        return jsonify({'error': 'Word not found'}), 404

# 获取单词列表
@app.route('/words', methods=['GET'])
def get_words():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM words')
    words = cursor.fetchall()
    conn.close()
    return jsonify([dict(word) for word in words])

@app.route('/words', methods=['POST'])
def add_or_update_word():
    data = request.get_json()
    word_id = data.get('id')  # 如果存在 id，则表示是更新操作

    word = data.get('word')
    translation = data.get('translation')
    definition = data.get('definition')
    star = data.get('star')

    if not word:
        return jsonify({'error': 'Word is required'}), 400

    conn = get_db_connection()
    cursor = conn.cursor()

    if word_id:  # 更新操作
        # 查询是否存在该记录
        cursor.execute('SELECT * FROM words WHERE id = ?', (word_id,))
        existing_word = cursor.fetchone()
        if not existing_word:
            conn.close()
            return jsonify({'error': 'Word not found'}), 404

        # 构造更新字段和参数
        update_fields = {}
        if 'word' in data and data['word'] is not None:
            update_fields['word'] = data['word']
        if 'translation' in data and data['translation'] is not None:
            update_fields['translation'] = data['translation']
        if 'definition' in data and data['definition'] is not None:
            update_fields['definition'] = data['definition']
        if 'star' in data and data['star'] is not None:
            update_fields['star'] = data['star']

        if not update_fields:
            conn.close()
            return jsonify({'message': 'No fields to update'}), 400

        set_clause = ', '.join([f'{field} = ?' for field in update_fields.keys()])
        values = list(update_fields.values())
        values.append(word_id)

        cursor.execute(f'UPDATE words SET {set_clause} WHERE id = ?', values)
        message = 'Word updated successfully'
    else:  # 添加操作
        cursor.execute('INSERT INTO words (word, translation, definition, star) VALUES (?, ?, ?, ?)',
                       (word, translation, definition, star))
        message = 'Word added successfully'

    conn.commit()
    conn.close()
    return jsonify({'message': message})

if __name__ == '__main__':
    app.run(debug=True)